package org.kl.auditsupervision.repository.fnd;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.kl.auditsupervision.dto.audit.InControlTypeStatistic;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component
public class InControlTypeStatisticDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	public List<InControlTypeStatistic> getInControlTypeStatistic(String startYear,String endYear,String type,
			int pageNumber, int pageSize, String sortType, String orderType) {
		final List<InControlTypeStatistic> result = new ArrayList<>();
		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString=getSql(startYear,endYear,type, sortType, orderType);
		if(pageNumber==0&&pageSize==0){
			//excel不分页
		}else{
			sqlString=getPaginationSql(sqlString,pageNumber,pageSize);
		}
		sql.append(sqlString);

		this.jdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
			int i = 0;

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				InControlTypeStatistic inControlTypeStatistic = new InControlTypeStatistic();
				inControlTypeStatistic.setChildDept(rs.getString("org_name"));
				inControlTypeStatistic.setBgsgl(rs.getString("bgsgl"));
				inControlTypeStatistic.setCgyw(rs.getString("cgyw"));
				inControlTypeStatistic.setCwbg(rs.getString("cwbg"));
				inControlTypeStatistic.setFwgl(rs.getString("fwgl"));
				inControlTypeStatistic.setGcxm(rs.getString("gcxm"));
				inControlTypeStatistic.setShzr(rs.getString("shzr"));
				inControlTypeStatistic.setWbyw(rs.getString("wbyw"));
				inControlTypeStatistic.setXsyw(rs.getString("xsyw"));
				inControlTypeStatistic.setXygl(rs.getString("xygl"));
				inControlTypeStatistic.setZcgl(rs.getString("zcgl"));
				inControlTypeStatistic.setZjhd(rs.getString("zjhd"));
				inControlTypeStatistic.setZzjg(rs.getString("zzjg"));
				
				inControlTypeStatistic.setCount(rs.getString("count"));
				i++;
				inControlTypeStatistic.setDisplaySeq(Integer.parseInt(i + "0"));
				result.add(inControlTypeStatistic);
			}
		});
		return result;
	}

	private String getPaginationSql(String sql, int pageNumber, int pageSize) {
		String startRow = String.valueOf(pageSize * (pageNumber-1));
		String endRow = String.valueOf(pageSize * pageNumber);
		String paginationSql = "";
		paginationSql = "select * from (select row_.*, rownum rownum_ from ("
				+ sql + ")row_ where rownum <= " + endRow
				+ ")where rownum_ > " + startRow;
		return paginationSql;
	}
	
	private String getSql(String startYear,String endYear,String type, String sortType, String orderType){
		String sqlString = "";
	
		sqlString += "select '项数' as org_name, "+
       "count(decode(de.nkfl, '组织架构', 1)) as zzjg, "+
       "count(decode(de.nkfl, '资金活动', 1)) as zjhd, "+
       "count(decode(de.nkfl, '资产管理', 1)) as zcgl, "+
       "count(decode(de.nkfl, '销售业务', 1)) as xsyw, "+
       "count(decode(de.nkfl, '采购业务', 1)) as cgyw, "+
       "count(decode(de.nkfl, '工程项目', 1)) as gcxm, "+
       "count(decode(de.nkfl, '信用管理', 1)) as xygl, "+
       "count(decode(de.nkfl, '办公室(含信息传递)管理', 1)) as bgsgl, "+
       "count(decode(de.nkfl, '法务（含合同）管理', 1)) as fwgl, "+
       "count(decode(de.nkfl, '外包业务', 1)) as wbyw, "+
       "count(decode(de.nkfl, '社会责任', 1)) as shzr, "+
       "count(decode(de.nkfl, '财务报告', 1)) as cwbg, "+
       "count(de.nkfl) as count "+
  "from sjjc_audit_defect de "+
  "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
  "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
  "left join fnd_org o on o.id = dtl.org_id where 1=1";
		  if(StringUtils.isNotBlank(startYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
		  }
		  if(StringUtils.isNotBlank(endYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
		  }
		  if(StringUtils.isNotBlank(type)){
			  sqlString+=" and a.type = '"+type+"' ";
		  }
		  sqlString+="union all "+
"select '比例(%)', "+
       "round(count(decode(de.nkfl, '组织架构', 1)) / count(de.nkfl) * 100, 0) as zzjg, "+
       "round(count(decode(de.nkfl, '资金活动', 1)) / count(de.nkfl) * 100, 0) as zjhd, "+
       "round(count(decode(de.nkfl, '资产管理', 1)) / count(de.nkfl) * 100,0) as zcgl, "+
       "round(count(decode(de.nkfl, '销售业务', 1)) / count(de.nkfl) * 100, 0) as xsyw, "+
       "round(count(decode(de.nkfl, '采购业务', 1)) / count(de.nkfl) * 100, 0) as cgyw, "+
       "round(count(decode(de.nkfl, '工程项目', 1)) / count(de.nkfl) * 100, 0) as gcxm, "+
       "round(count(decode(de.nkfl, '信用管理', 1)) / count(de.nkfl) * 100, 0) as xygl, "+
       "round(count(decode(de.nkfl, '办公室(含信息传递)管理', 1)) / count(de.nkfl) * 100,0) as bgsgl, "+
       "round(count(decode(de.nkfl, '法务（含合同）管理', 1)) / count(de.nkfl) * 100, 0) as fwgl, "+
       "round(count(decode(de.nkfl, '外包业务', 1)) / count(de.nkfl) * 100, 0) as wbyw, "+
       "round(count(decode(de.nkfl, '社会责任', 1)) / count(de.nkfl) * 100, 0) as shzr, "+
       "round(count(decode(de.nkfl, '财务报告', 1)) / count(de.nkfl) * 100, 0) as cwbg, "+
       "1 "+
  "from sjjc_audit_defect de "+
  "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
  "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
  "left join fnd_org o on o.id = dtl.org_id where 1=1";
		  if(StringUtils.isNotBlank(startYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
		  }
		  if(StringUtils.isNotBlank(endYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
		  }
		  if(StringUtils.isNotBlank(type)){
			  sqlString+=" and a.type = '"+type+"' ";
		  }
  sqlString+="union all "+
"select t.org_name, "+
		"count(decode(t.nkfl, '组织架构', 1)) as zzjg, "+
		"count(decode(t.nkfl, '资金活动', 1)) as zjhd, "+
		"count(decode(t.nkfl, '资产管理', 1)) as zcgl, "+
		"count(decode(t.nkfl, '销售业务', 1)) as xsyw, "+
		"count(decode(t.nkfl, '采购业务', 1)) as cgyw, "+
		"count(decode(t.nkfl, '工程项目', 1)) as gcxm, "+
		"count(decode(t.nkfl, '信用管理', 1)) as xygl, "+
		"count(decode(t.nkfl, '办公室(含信息传递)管理', 1)) as bgsgl, "+
		"count(decode(t.nkfl, '法务（含合同）管理', 1)) as fwgl, "+
		"count(decode(t.nkfl, '外包业务', 1)) as wbyw, "+
		"count(decode(t.nkfl, '社会责任', 1)) as shzr, "+
		"count(decode(t.nkfl, '财务报告', 1)) as cwbg, "+
       "count(t.nkfl) as count "+
  "from (select o.org_name, de.* "+
          "from sjjc_audit_defect de "+
          "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
          "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
          "left join fnd_org o on o.id = dtl.org_id where 1=1";
        		  if(StringUtils.isNotBlank(startYear)){
        			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
        		  }
        		  if(StringUtils.isNotBlank(endYear)){
        			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
        		  }
        		  if(StringUtils.isNotBlank(type)){
        			  sqlString+=" and a.type = '"+type+"' ";
        		  }
          sqlString+=") t group by t.org_name ";

		return sqlString;
	}
	
	public int getInControlTypeStatisticCount(String startYear,String endYear,String type) {

		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString=getSql(startYear,endYear,type,"","");

		sql.append("select count(1) as count from ("+sqlString+")");
		
		Integer count=(Integer)this.jdbcTemplate.queryForObject(sql.toString(),Integer.class);
		
		int coungInt=count.intValue();

		return coungInt;
	}

}
